import os
import re
from datetime import datetime, timedelta
import numpy as np

import pandas as pd
from PyQt5.QtWidgets import QMessageBox
from openpyxl.styles import Border, Side, Font, Alignment, PatternFill, colors
from openpyxl.utils import get_column_letter
from openpyxl.workbook import Workbook

thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)

# 创建并设置大标题的字体样式，24加粗
header_font = Font()
header_font.name = 'Arial'
header_font.size = 24
header_font.bold = True

# 定义标题的字体样式，20不加粗
title_font = Font()
title_font.name = 'Arial'
title_font.size = 20

# 定义字体样式，16不加粗
text1_font = Font()
text1_font.name = 'Arial'
text1_font.size = 16

# 定义字体样式，16加粗
text2_font = Font()
text2_font.name = 'Arial'
text2_font.size = 16
text2_font.bold = True

# 设置单元格对齐方式，居中对齐
center_center_alignment = Alignment()
center_center_alignment.horizontal = 'center'
center_center_alignment.vertical = 'center'

# 设置单元格对齐方式，左对齐
left_center_alignment = Alignment()
left_center_alignment.horizontal = 'left'
left_center_alignment.vertical = 'center'

# 设置单元格对齐方式，右对齐
right_center_alignment = Alignment()
right_center_alignment.horizontal = 'right'
right_center_alignment.vertical = 'center'

# 设置单元格对齐方式，左对齐
left_bottom_alignment = Alignment()
left_bottom_alignment.horizontal = 'left'
left_bottom_alignment.vertical = 'bottom'

# 设置单元格对齐方式，右对齐
right_bottom_alignment = Alignment()
right_bottom_alignment.horizontal = 'right'
right_bottom_alignment.vertical = 'bottom'

# 定义背景颜色，浅绿色
text1_fill_pattern = PatternFill()
text1_fill_pattern.start_color = colors.Color(rgb='E2EFDA')
text1_fill_pattern.end_color = colors.Color(rgb='E2EFDA')
text1_fill_pattern.fill_type = 'solid'

# 定义背景颜色，浅橙色
text2_fill_pattern = PatternFill()
text2_fill_pattern.start_color = colors.Color(rgb='FCE4D6')
text2_fill_pattern.end_color = colors.Color(rgb='FCE4D6')
text2_fill_pattern.fill_type = 'solid'

# 后座
backseat = (
    '后腿肉', '后腿肉-精瘦', '后腿肉-精瘦肉丝', '后腿肉-精瘦肉片', '后腿肉-精瘦肉沫',
    # '整猪后座',
    '带骨后座A',
    '带骨带蹄后座',
    '带皮后段(去蹄)', '后腿瘦肉',
    '后腿肉丝', '后腿肉丝-肥瘦分开',
    '后腿肉片-带皮', '后腿肉片-去皮', '后腿肉片-肥瘦分开',
    '后座底板',
    '后腿肉沫',
    '里脊肉', '里脊肉丝', '里脊肉片', '小里脊-腰条', '带膘里脊',  # 里脊
    '纯瘦肉',
    '整后座带蹄',
    '去肘后段',
)
# 前夹
front_clip = (
    '前夹肉',
    '前夹肉切小块-带皮',
    '前夹肉-精瘦',
    '前夹肉-去皮',
    '前夹肉-去皮-肥瘦分开',
    # '整猪前夹',
    '带骨前夹A',
    '去前段带前排',
    '带骨带蹄前夹',
    '前夹肉丝',
    '前夹肉丝-肥瘦分开',
    '前夹肉丝-精瘦',
    '前夹肉片-带皮',
    '前夹肉片-去皮',
    '前夹肉片-纯瘦',
    '前夹肉沫',
    '前夹带骨带前排',
    '肥肉',
    '肥膘',
    '肥膘M',  # 前夹
    '前段带前排带蹄',
    '包心肉',  # 包心肉
)
# 排骨
spareribs = (
    '1中排', '1中排-剁小块', '1中排-剁条',
    '大中段', '打膘中段',
    '整猪中方',
    '1通排', '1通排-剁小块',
    '有颈通排',
    '脊骨', '脊骨-剁小块',
    '扇子骨', '扇子骨-剁小块',
    '猪杂骨',
    '筒子骨', '筒子骨-切小段', '筒子骨-敲断',  # 筒子骨
    '隔子肉',  # 隔子肉
    '前排',
    '去颈骨带尾骨通排',
    '带尾叉通排',
    '尾叉骨', '尾叉骨-剁小块',
)
# 猪蹄
pig_trotters = (
    '前猪蹄', '前猪蹄-剁块', '前猪蹄-烧毛',
    '后猪蹄', '后猪蹄-剁块', '后猪蹄-烧毛',
    '一对猪蹄', '一对猪蹄-烧毛',
    '前后蹄(对蹄)', '前后蹄(对蹄)-烧毛',
    '猪蹄髈', '猪蹄髈-剁小块',
    '猪手', '猪手-剁小块', '猪手-劈两半'
)
# 五花
streaky = (
    '精品五花', '精品五花-带皮切片', '精品五花-红烧肉块', '精品五花-切条',
    '精修大五花a',
    # '里脊肉', '里脊肉丝', '里脊肉片', '小里脊-腰条', '带膘里脊',  # 里脊
)
# 白条
white_strips = (
    '一二级精白条', '三级精白条', '一二级毛白条', '三级毛白条',
    '前中后段不带蹄', '白条不带蹄', '中后段不带蹄',
    '一二级精白条前中段', '前中段带蹄', '前中段不带蹄', '中后段带蹄',
    '黑猪带板油带猪脚去槽头下泡白条'
)
# 副产品
by_product = (
    '1猪腰', '猪肝', '猪肚', '猪大肠', '猪小肠', '猪心肺', '1猪板油', '1猪板油-切小块', '1猪血', '1猪血-盒装', '猪肝-切片',
    '肺叶', '猪-心',
    '脆骨',  # 脆骨
    '预制肥肠',
    '2蛋鸡',  # 蛋鸡
    '2三黄鸡',  # 蛋鸡
    '碎肉',  # 碎肉

    # '纯瘦肉',
    '蹄膀M', '猪皮M',
    '猪耳朵M',
    '狗猪头', '平猪头', '脑花', '带耳头皮', '不带耳头皮', '带肉中耳', '精耳片', '毛口条', '精口条', '猪脸', '毛狗猪头',
    '毛平猪头',  # 猪头
    'o2o打包费', 'o2o配送费', 'o2o其他费用', 'o2o外卖'  # 其他类别
)

# # 路线对应运货司机
# delivery_driver = {
#     '2': '谭克发',
#     '3': '刘烁',
#     '33': '陈涛',
#     '7': '胡荣华',
#     '4': '刘先华',
#     '42': '胡荣华',
#     '5': '何洲斌,',
#     '6': '朱敬文',
#     '62': '李启进,禹师傅',
#     '': None,
# }

keywords_to_exclude = [r'\*', '-', '1队', '2队', '3队','#','&']

# Supermarket Catering (Additional Order Form)
# 商超餐饮(加单表)

# 自动调整列宽
def adjust_column_width(worksheet, start_row=1, num_columns=15):
    """根据内容自动调整工作表的指定列数的列宽，从指定行开始计算"""
    for i in range(num_columns):  # 只处理前num_columns列
        column = worksheet[get_column_letter(i + 1)]  # 获取列，如'A', 'B', 'C'...
        max_length = 0
        column_letter = get_column_letter(i + 1)  # 获取列字母
        # 从指定的行开始迭代
        for cell in column[start_row:]:
            try:  # 尝试获取单元格的字符串长度，跳过空值或非字符串类型的错误
                if cell.value is not None:
                    if isinstance(cell.value, float):
                        if np.isnan(cell.value):
                            continue
                        max_length = len(str(round(cell.value, 1)))
                    elif len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                else:
                    if not max_length:
                        max_length = 4
            except TypeError:
                pass

        if num_columns == 15:
            if i == 0:
                adjusted_width = 16.91
            elif i in (1, 2):
                adjusted_width = 2.72 * max_length + 3.575
            elif i in (3, 4, 5, 6):
                adjusted_width = 0.8007 * max_length + 5.7932
            else:
                adjusted_width = 3.2725 * max_length + 0.275
        elif num_columns == 16:
            if i == 0:
                adjusted_width = 2.72 * max_length + 3.575
            elif i in (1, 2, 3, 4, 5):
                adjusted_width = 0.8007 * max_length + 5.7932
            else:
                adjusted_width = 2.72 * max_length + 3.575
        else:
            adjusted_width = 2.67 * max_length + 4.59
        worksheet.column_dimensions[column_letter].width = adjusted_width


# 处理 配货数量单位
def safe_extract_and_update_company(data_row):
    # 提取公司信息
    if pd.notnull(data_row.iloc[6]):  # 第七列
        match = re.search(r'/([^/]+)$', str(data_row.iloc[6]))
        if match:
            company = match.group(1)
            if company == '边':
                company = '头'
            result = company
        else:
            # 如果没有找到匹配，可以定义一个默认行为
            result = '(未知单位)'
    else:
        result = data_row.iloc[5]

    return result


# 定义展示信息
def show_warning_message(self, warning_type):
    """展示警告消息"""
    if warning_type == 'file_selection':
        QMessageBox.warning(self, '警告', self.FILE_SELECTION_WARNING)
    elif warning_type == 'wrong_file_format':
        QMessageBox.warning(self, '警告', self.WRONG_FILE_FORMAT_WARNING)


# 新增方法：检查文件是否为Excel格式
def _is_excel_file(self, file_path):
    _, extension = os.path.splitext(file_path)
    return extension.lower() in self.ACCEPTED_EXCEL_EXTENSIONS


# 检查并返回Excel文件路径
def check_and_return_excel_path2(self):
    self.file_path = self.textEdit.toPlainText()
    self.file_path2 = self.textEdit_2.toPlainText()
    if (self.file_path == '') and (self.file_path2 == ''):
        QMessageBox.warning(self, '警告', '请添加(订单状态跟踪表)和(订单状态跟踪表-加单)文件！')
        return None, None
    if self.file_path and (self.file_path2 == ''):
        QMessageBox.warning(self, '警告', '请添加(订单状态跟踪表-加单)文件！')
        return None, None
    if (self.file_path == '') and self.file_path2:
        QMessageBox.warning(self, '警告', '请添加(订单状态跟踪表)文件！')
        return None, None
    # 如果路径中包含特定分隔符序列，则仅保留最后一部分
    if self.PATH_SEPARATOR in self.file_path:
        self.file_path = self.file_path.split(self.PATH_SEPARATOR)[-1]
    if self.PATH_SEPARATOR in self.file_path2:
        self.file_path2 = self.file_path2.split(self.PATH_SEPARATOR)[-1]

    file_path = self.file_path
    file_path2 = self.file_path2
    if not file_path:
        show_warning_message(self, self.FILE_SELECTION_WARNING)
        return None, None
    if not file_path2:
        show_warning_message(self, self.FILE_SELECTION_WARNING)
        return None, None

    if _is_excel_file(self, file_path) and _is_excel_file(self, file_path2):
        return file_path, file_path2
    else:
        show_warning_message(self, self.WRONG_FILE_FORMAT_WARNING)
        return None, None


# 根据文件名称打开excel表格，并获取所有数据
def get_two_all_excel_data(self):
    file_path, file_path2 = check_and_return_excel_path2(self)
    if file_path and file_path2:
        data_frames = pd.read_excel(file_path, sheet_name=None)
        data_frames2 = pd.read_excel(file_path2, sheet_name=None)
        # 假设Excel只有一个工作表，可以直接访问第一个数据框
        if data_frames and data_frames2:
            df = list(data_frames.values())[0]  # 这里取第一个工作表的数据，如果有多个工作表，需要根据实际情况选择
            all_datas2 = list(data_frames2.values())[0]
            return df, all_datas2
        else:
            QMessageBox.warning(self, '警告', 'Excel中没有数据')
            return None, None
    else:
        return None, None


# 处理 商超餐饮(加单表) 数据
def process_super_cater_order_workshop_table_add(self):
    all_datas, all_datas2 = get_two_all_excel_data(self)
    if all_datas is not None and all_datas2 is not None:

        all_datas = all_datas.iloc[4:]
        # 匹配所有未出货的数据
        all_datas = all_datas[all_datas.iloc[:, 3] == '未出货']
        # 使用str.contains检查第二列中是否不包含任何关键字，注意使用~进行取反操作
        all_datas = all_datas[~all_datas[all_datas.columns[1]].astype(str).str.contains('|'.join(keywords_to_exclude))]

        all_datas2 = all_datas2.iloc[4:]
        # 匹配所有未出货的数据
        all_datas2 = all_datas2[all_datas2.iloc[:, 3] == '未出货']
        # 使用str.contains检查第二列中是否不包含任何关键字，注意使用~进行取反操作
        all_datas2 = all_datas2[
            ~all_datas2[all_datas2.columns[1]].astype(str).str.contains('|'.join(keywords_to_exclude))]

        # 找出all_datas2中第三列（'Column3'）与all_datas中第三列值相同的所有行的索引
        duplicate_indices = all_datas2[all_datas2['Unnamed: 2'].isin(all_datas['Unnamed: 2'])].index

        # 从all_datas2中移除这些行
        all_datas2_filtered = all_datas2.drop(index=duplicate_indices)

        # 在DataFrame中创建一个新列用于存放匹配结果，默认值为None或空字符串
        all_datas2_filtered['Driver_Name'] = None
        # 在DataFrame中创建一个新列用于存放计算结果，默认值可设置为NaN，配货数量，Quantity of goods allocated
        all_datas2_filtered['Quantity_Allocated'] = None  # 或者使用 None，取决于后续处理需求

        # 遍历DataFrame的每一行，尝试从第二列提取数字并匹配字典
        for index, row in all_datas2_filtered.iterrows():
            # 使用正则表达式从第二列提取数字
            match = re.search(r'\d+', str(row.values[1]))
            if match:
                number = match.group()
                # 尝试从字典中获取对应司机的姓名
                driver_name = self.delivery_driver.get(number, '未知司机')
                # 如果找到了匹配的司机姓名，则更新该行的新列
                all_datas2_filtered.at[index, 'Driver_Name'] = driver_name
            else:
                all_datas2_filtered.at[index, 'Driver_Name'] = '未知司机'

            if pd.notnull(row.values[6]):
                match = re.search(r'\d+(\.\d+)?', row.values[6])
                # 检查第七列是否有值
                if match:  # 索引从0开始，所以第7列是索引6
                    number = float(match.group())
                    # 如果有值，则进行除法运算，并将结果添加到 result_column 列表中
                    if row.values[4] in ('一二级精白条','三级精白条','一二级毛白条','三级毛白条'):
                        result = round(row.values[7] / 2 / number, 1)
                    else:
                        result = round(row.values[7] / number, 1)
                    all_datas2_filtered.at[index, 'Quantity_Allocated'] = result
            else:
                # 如果第七列没有值，可以决定如何处理，这里选择添加 NaN
                all_datas2_filtered.at[index, 'Quantity_Allocated'] = row.values[7]

        sorted_data = all_datas2_filtered.sort_values(
            by=[all_datas2_filtered.columns[1], all_datas2_filtered.columns[4]])
        # 使用assign方法来创建一个新的列或修改现有列，这能减少因数据视图引起的问题
        sorted_data = sorted_data.assign(Company=sorted_data.apply(safe_extract_and_update_company, axis=1, args=()))
        re_sorted_data = sorted_data.iloc[:, [21, 1, 4, 22, 23, 7, 5, 11]]

        # 计算第4列的合计
        total4 = re_sorted_data.iloc[:, 3].sum()

        # 计算第6列的合计
        total = re_sorted_data.iloc[:, 5].sum()

        # 准备合计行数据，使用None填充非合计值的列
        new_row = pd.Series(['合计'] + [None] * 2 + [total4] + [None] + [total] + [None] * 2,
                            index=re_sorted_data.columns)

        # 使用pd.concat追加合计行
        re_sorted_data = pd.concat([re_sorted_data, new_row.to_frame().T], ignore_index=True)
        return re_sorted_data
    else:
        return None


# 创建 商超餐饮(加单表) 表格
def create_super_cater_order_workshop_table_add(self, all_datas):
    if all_datas is not None:
        add_production_data = all_datas

        wb = Workbook()

        default_sheet = wb.active
        wb.remove(default_sheet)

        # 创建工作簿
        sht0 = wb.create_sheet(title="加单数据表")

        sht0.row_dimensions[1].height = 30

        # 定义大标题内容
        header_name = (datetime.now() + timedelta(hours=8)).strftime('%Y-%m-%d-') + '商超餐饮-[加单](订单明细)'

        # 合并单元格从 'A1' 到 'H1'
        sht0.merge_cells(start_row=1, start_column=1, end_row=1, end_column=8)

        # 写入大标题并应用样式
        sht0['A1'] = header_name
        sht0['A1'].font = header_font
        sht0['A1'].border = thin_border
        sht0['A1'].alignment = center_center_alignment

        sht0.row_dimensions[2].height = 25
        sht0.row_dimensions[3].height = 25

        sht0['A2'] = "送货司机"
        sht0['A2'].font = title_font
        sht0['A2'].border = thin_border
        sht0['A2'].alignment = center_center_alignment

        sht0['A3'] = "送货司机"
        sht0['A3'].font = title_font
        sht0['A3'].border = thin_border
        sht0['A3'].alignment = center_center_alignment

        sht0['B2'] = "客户名称"
        sht0['B2'].font = title_font
        sht0['B2'].border = thin_border
        sht0['B2'].alignment = center_center_alignment

        sht0['B3'] = "客户名称"
        sht0['B3'].font = title_font
        sht0['B3'].border = thin_border
        sht0['B3'].alignment = center_center_alignment

        sht0['C2'] = "品名"
        sht0['C2'].font = title_font
        sht0['C2'].border = thin_border
        sht0['C2'].alignment = center_center_alignment

        sht0['C3'] = "品名"
        sht0['C3'].font = title_font
        sht0['C3'].border = thin_border
        sht0['C3'].alignment = center_center_alignment

        sht0['D2'] = "配货数量"
        sht0['D2'].font = text2_font
        sht0['D2'].border = thin_border
        sht0['D2'].alignment = center_center_alignment

        sht0['D3'] = "数量"
        sht0['D3'].font = text1_font
        sht0['D3'].border = thin_border
        sht0['D3'].alignment = center_center_alignment

        sht0['E2'] = "配货数量"
        sht0['E2'].font = text2_font
        sht0['E2'].border = thin_border
        sht0['E2'].alignment = center_center_alignment

        sht0['E3'] = "单位"
        sht0['E3'].font = text1_font
        sht0['E3'].border = thin_border
        sht0['E3'].alignment = center_center_alignment

        sht0['F2'] = "订单数量"
        sht0['F2'].font = text2_font
        sht0['F2'].border = thin_border
        sht0['F2'].alignment = center_center_alignment

        sht0['F3'] = "数量"
        sht0['F3'].font = text1_font
        sht0['F3'].border = thin_border
        sht0['F3'].alignment = center_center_alignment

        sht0['G2'] = "订单数量"
        sht0['G2'].font = text2_font
        sht0['G2'].border = thin_border
        sht0['G2'].alignment = center_center_alignment

        sht0['G3'] = "单位"
        sht0['G3'].font = text1_font
        sht0['G3'].border = thin_border
        sht0['G3'].alignment = center_center_alignment

        sht0['H2'] = "备注"
        sht0['H2'].font = title_font
        sht0['H2'].border = thin_border
        sht0['H2'].alignment = center_center_alignment

        sht0['H3'] = "备注"
        sht0['H3'].font = title_font
        sht0['H3'].border = thin_border
        sht0['H3'].alignment = center_center_alignment

        # 合并成所需单元格样式
        sht0.merge_cells(start_row=2, start_column=1, end_row=3, end_column=1)
        sht0.merge_cells(start_row=2, start_column=2, end_row=3, end_column=2)
        sht0.merge_cells(start_row=2, start_column=3, end_row=3, end_column=3)
        sht0.merge_cells(start_row=2, start_column=4, end_row=2, end_column=5)
        sht0.merge_cells(start_row=2, start_column=6, end_row=2, end_column=7)
        sht0.merge_cells(start_row=2, start_column=8, end_row=3, end_column=8)

        i = 4
        # 初始化一个字典来存储需要合并的单元格范围
        merge_ranges = {}

        # 写入数据并记录需要合并的单元格
        last_col_a_value = None

        # 写入数据
        for index, row in add_production_data.iterrows():
            sht0.row_dimensions[i].height = 20
            j = 1
            for col in row:
                if j == 1:
                    cell = sht0.cell(row=i, column=j, value=col)
                    cell.font = text1_font
                    cell.border = thin_border
                    cell.alignment = center_center_alignment
                elif j == 2:
                    cell = sht0.cell(row=i, column=j, value=col)
                    cell.font = text1_font
                    cell.border = thin_border
                    cell.alignment = left_center_alignment
                    cell.fill = text1_fill_pattern
                elif j == 3:
                    cell = sht0.cell(row=i, column=j, value=col)
                    cell.font = text1_font
                    cell.border = thin_border
                    cell.alignment = left_center_alignment
                elif j == 4:
                    if col is not None:
                        col = f"{col:.1f}" if col % 1 != 0 else int(col)
                    else:
                        col = None
                    cell = sht0.cell(row=i, column=j, value=col)
                    cell.font = text1_font
                    cell.border = thin_border
                    cell.alignment = center_center_alignment
                    cell.fill = text2_fill_pattern
                elif j == 5:
                    cell = sht0.cell(row=i, column=j, value=col)
                    cell.font = text1_font
                    cell.border = thin_border
                    cell.alignment = center_center_alignment
                    cell.fill = text2_fill_pattern
                elif j == 6:
                    cell = sht0.cell(row=i, column=j, value=col)
                    cell.font = text2_font
                    cell.border = thin_border
                    cell.alignment = center_center_alignment
                elif j == 7:
                    cell = sht0.cell(row=i, column=j, value=col)
                    cell.font = text2_font
                    cell.border = thin_border
                    cell.alignment = center_center_alignment
                elif j == 8:
                    cell = sht0.cell(row=i, column=j, value=col)
                    cell.font = text1_font
                    cell.border = thin_border
                    cell.alignment = left_center_alignment
                j = j + 1

            # 检查当前行A列（第二列）的值是否与上一行相同
            if row.iloc[1] == last_col_a_value:
                # 如果相同，记录这个范围需要被合并
                if row.iloc[1] not in merge_ranges:
                    merge_ranges[row.iloc[1]] = [i - 1, i]
                else:
                    merge_ranges[row.iloc[1]][1] = i
            else:
                # 如果不同，更新上一个值并继续
                if last_col_a_value is not None:
                    sht0.merge_cells(start_row=merge_ranges[last_col_a_value][0], start_column=2,
                                     end_row=merge_ranges[last_col_a_value][1], end_column=2)
                last_col_a_value = row.iloc[1]
                merge_ranges[row.iloc[1]] = [i, i]

            i = i + 1

        # 确保处理最后一组需要合并的单元格
        if last_col_a_value in merge_ranges:
            sht0.merge_cells(start_row=merge_ranges[last_col_a_value][0], start_column=2,
                             end_row=merge_ranges[last_col_a_value][1], end_column=2)

        adjust_column_width(sht0, start_row=3, num_columns=15)

        # 在数据之后添加两列
        last_row = merge_ranges[last_col_a_value][1]

        sht0.row_dimensions[last_row + 1].height = 40

        cell = sht0.cell(row=last_row + 1, column=2, value="送货司机签字:")
        cell.font = text2_font
        cell.alignment = right_bottom_alignment

        cell = sht0.cell(row=last_row + 1, column=row.size - 2, value="收货人签字:")
        cell.font = text2_font
        cell.alignment = left_bottom_alignment

        # 获取格式化的年月日字符串
        formatted_date = (datetime.now() + timedelta(hours=8)).strftime('%Y%m%d_%H%M%S')
        file_name = '/商超餐饮(加单表)_' + formatted_date + '.xls'
        file_path = self.file_path
        pattern = r'(.+?)(?=/[^/]*$)'
        match = re.search(pattern, file_path)
        file_path = match.group(1)
        file_name_path = file_path + file_name

        # 保存文件到路径
        wb.save(file_name_path)

        QMessageBox.warning(self, '提示', '数据导出成功！')
